package com.b2c.repository.oms;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.datacenter.ErpGoodsExprotVo;
import com.b2c.entity.erp.vo.ErpGoodsListVo;
import com.b2c.entity.erp.vo.ErpGoodsSpecListVo;
import com.b2c.entity.erp.vo.ErpGoodsSpecStockListVo;
import com.b2c.entity.erp.vo.ErpGoodsSpecStockVo;
import com.b2c.repository.Tables;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

@Repository
public class DataCenterInvoiceRepository {
    @Autowired
    JdbcTemplate jdbcTemplate;

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 商品库存查询
     *
     * @param pageIndex
     * @param pageSize
     * @param
     * @return
     */
    @Transactional
    public PagingResponse<ErpGoodsSpecListVo> getSpecInventoryList(int pageIndex, int pageSize, String goodsNum, String goodsSpecNum) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS gs.specNumber,gs.id,gs.goodsId,IFNULL(gs.color_image,g.image) as image,g.name,g.number as goodsNumber,gs.color_value,gs.size_value,gs.style_value");
        sb.append(", IFNULL((SELECT IFNULL(SUM(currentQty),0) FROM erp_goods_stock_info WHERE specId=gs.id AND isDelete=0),0) AS currentQty ");
        sb.append(", IFNULL((SELECT SUM(it1.quantity) FROM erp_order_item AS it1 WHERE it1.skuId=gs.id AND  it1.`status` <= 1 ),0) AS pickingQty ");
        sb.append(" FROM ").append(Tables.ErpGoodsSpec).append(" as gs ");
        sb.append(" LEFT JOIN ").append(Tables.ErpGoods).append(" as g ON g.id=gs.goodsId");
        sb.append(" WHERE gs.isDelete=0 and g.isDelete=0 ");
        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(goodsNum)) {
            sb.append(" AND g.number like ? ");
            params.add("%" + goodsNum + "%");
        }
        if (!StringUtils.isEmpty(goodsSpecNum)) {
            sb.append(" AND gs.specNumber like ? ");
            params.add("%" + goodsSpecNum + "%");
        }

        sb.append("ORDER BY g.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        List<ErpGoodsSpecListVo> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsSpecListVo.class), params.toArray(new Object[params.size()]));

        return new PagingResponse<>(pageIndex, pageSize,getTotalSize() , list);
    }

    /**
     * 商品库存查询,不分页
     *
     * @param str
     * @return
     */
    public List<ErpGoodsExprotVo> getSpecInventoryList(String str) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS ");
        sb.append(" A.name,number,specNumber,currentQty,color_value,size_value,style_value,U.`name` AS unitName ");
        sb.append("  FROM erp_goods A LEFT JOIN erp_goods_spec B ON A.id=B.goodsId LEFT JOIN erp_unit U ON A.unitId=U.id ");
        sb.append(" WHERE A.isDelete=0 ");
        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(str)) {
            sb.append("AND number LIKE ? ");
            params.add("%" + str + "%");
        }
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsExprotVo.class),params.toArray(new Object[params.size()]));
    }

    /**
     * 商品上架时间
     * @param pageIndex
     * @param pageSize
     * @return
     */
    public PagingResponse<ErpGoodsSpecStockListVo> getGoodsList(int pageIndex, int pageSize){
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS ");
        sb.append("id,name,number,`disable`,createTime createTimeL ");
        sb.append("FROM").append(Tables.ErpGoods);
        sb.append(" WHERE disable=0 AND isDelete=0 ");
        List<Object> params = new ArrayList<>();
        sb.append("ORDER BY createTime DESC, id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        List<ErpGoodsSpecStockListVo> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsSpecStockListVo.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    /**
     * Sku更新时间
     * @param pageIndex
     * @param pageSize
     * @return
     */
    public PagingResponse<ErpGoodsSpecStockListVo> getGoodsSpecList(int pageIndex, int pageSize) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS ");
        sb.append("C.id,A.name,A.`disable`,A.createTime createTimeL,C.specNumber,C.currentQty,C.color_value,C.size_value,C.style_value ");
        sb.append("FROM").append(Tables.ErpGoods).append(" A  ");
        sb.append("LEFT JOIN").append(Tables.ErpGoodsSpec).append("C ON C.goodsId=A.id");
        sb.append(" WHERE A.disable=0 AND A.isDelete=0 AND C.specNumber !='' ");
        List<Object> params = new ArrayList<>();
        sb.append("ORDER BY A.createTime DESC, C.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        List<ErpGoodsSpecStockListVo> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsSpecStockListVo.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }


    /**
     * 商品入库更新时间
     * @param pageIndex
     * @param pageSize
     * @return
     */
    public PagingResponse<ErpGoodsSpecStockListVo> getGoodsInvoiceList(int pageIndex, int pageSize ){
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS ");
        sb.append("A.createTime,B.id,B.name,B.`disable`,C.`name` locationName,D.specNumber,D.currentQty,D.color_value,D.size_value,D.style_value ");
        sb.append("FROM").append(Tables.ErpGoodsStockLogs).append(" A LEFT JOIN ").append(Tables.ErpGoods).append(" B ON B.id=A.goodsId ");
        sb.append("LEFT JOIN ").append(Tables.ErpStockLocation).append(" C ON C.id=B.locationId ");
        sb.append("LEFT JOIN").append(Tables.ErpGoodsSpec).append("D ON D.goodsId=B.id");
        sb.append(" WHERE A.type=1 ");
        List<Object> params = new ArrayList<>();
        sb.append("  ORDER BY A.createTime DESC, A.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        List<ErpGoodsSpecStockListVo> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsSpecStockListVo.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }
}
